package com.scuec.tourismwebsite.mapper;

import com.scuec.tourismwebsite.entity.FreeformPost;
import com.scuec.tourismwebsite.entity.Post;
import com.scuec.tourismwebsite.vo.AdminPost;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface FreeformPostMapper {
    @Select("SELECT \n" +
            "    fp.post_id,\n" +
            "    fp.category,\n" +
            "    fp.title,\n" +
            "    fp.content,\n" +
            "    fp.post_date,\n" +
            "    fp.status,\n" +
            "    fp.collect_counts,\n" +
            "    fp.comments_count,\n" +
            "    fp.image_1,fp.image_2,fp.image_3,\n"+
            "    fp.likes_count,\n" +
            "    a.username\n" +
            "FROM \n" +
            "    freeform_post fp\n" +
            "INNER JOIN \n" +
            "    admin a ON fp.admin_id = a.admin_id\n" +
            "ORDER BY \n" +
            "    fp.post_date DESC")
    List<AdminPost> findAPostAll();

    @Select("SELECT \n" +
            "    fp.post_id,\n" +
            "    fp.category,\n" +
            "    fp.title,\n" +
            "    fp.content,\n" +
            "    fp.post_date,\n" +
            "    fp.status,\n" +
            "    fp.views_count,\n" +
            "    fp.comments_count,\n" +
            "    fp.likes_count,\n" +
            "    a.username\n" +
            "FROM \n" +
            "    freeform_post fp\n" +
            "INNER JOIN \n" +
            "    admin a ON fp.admin_id = a.admin_id\n" +
            "WHERE \n" +
            "    a.username LIKE CONCAT('%', #{username}, '%')\n" +
            "ORDER BY fp.post_date")
    List<AdminPost> findAPostAllByUsername(String username);

    @Delete("DELETE FROM freeform_post WHERE post_id =#{post_id}")
    Integer deleteAPostById(Integer post_id);

    @Select("SELECT p.post_id, p.title, p.content, p.category, p.collect_counts, p.post_date, p.comments_count, p.likes_count, p.image_1, p.image_2, p.image_3, a.avatar, a.username, a.admin_id " +
            "FROM freeform_post p " +
            "INNER JOIN admin a ON p.admin_id = a.admin_id " +
            "WHERE p.post_id = #{post_id} " +
            "ORDER BY p.post_date DESC")
    AdminPost findAPostById(@Param("post_id") Integer postId);

    @Update("UPDATE freeform_post SET comments_count = #{comments_count},likes_count=#{likes_count},collect_counts=#{collect_counts} where post_id=#{post_id}")
    Integer updateAPostCount(@Param("post_id") Integer post_id,@Param("comments_count") Integer comments_count,@Param("likes_count") Integer likes_count,@Param("collect_counts") Integer collect_counts);

    @Insert("INSERT INTO freeform_post (post_id,admin_id,title,content,category,post_date,create_date,comments_count,likes_count,image_1,image_2,image_3,collect_counts) VALUES (#{post_id},#{admin_id}, #{title}, #{content},#{category},NOW(),NOW(),#{comments_count},#{likes_count},#{image_1},#{image_2},#{image_3},#{collect_counts})")
    Integer insertPost(FreeformPost freeformPost);
}


